/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.jgls.bi.dataprocess;

import java.util.List;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultReader;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;

/**
 *
 * @author Administrator
 */
public class biUnsalableSymbolJdbcDao extends JdbcDaoSupport implements biUnsalableDataDao {

    public void biUnsalableData(Date adTsnDate, int aiShopId, String asShopBm, int aiDateNo, String asYear, int aiYybh, int aiMan_id, String asTname) {
        int iShopCnt = 0;
        int iRc = 0;
        int aiYear = 0, aiWeek = 0;
        int i = 0;
        final int isize;
        final int isize2;
        final int iyear,  iweek;
        final Date dd;
        SimpleDateFormat ddd = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Calendar rightNow = Calendar.getInstance();
        rightNow.add(Calendar.DATE, -1);
        String lsYesterday = ddd.format(rightNow.getTime());
        java.sql.Date ldYesterday = java.sql.Date.valueOf(lsYesterday);

        if (adTsnDate.before(ldYesterday)) {
            //汇总表
            asTname = " t_spsjks_symbol ";
        } else {
            //工作表，只存储昨天和前天的数据
            asTname = " t_spsjks_symbol ";
            //sTname = " t_waste_book ";
        }
        

        //该程序只运行一次即可，所以目前定为2店，如2店闭店，请换为其他门店
        if (aiShopId != 2) {
            return;
        }
        String sql = "";
        String sLb = "";
        String sDate = "";
        String sXplbm = "";
        int iYybh = 0;
        int iPz = 0;
        ArrayList<String> alLb = new ArrayList<String>();
        ArrayList<Integer> aiSC = new ArrayList<Integer>();
        ArrayList<String> alLbYyb = new ArrayList<String>();
        ArrayList<Integer> aiSCYyb = new ArrayList<Integer>();
        ArrayList<Integer> aiYyb = new ArrayList<Integer>();
        ArrayList<Integer> aiPz = new ArrayList<Integer>();
        ArrayList<Integer> aiPzYyb = new ArrayList<Integer>();
        ArrayList<String> alXplbm = new ArrayList<String>();
        ArrayList<String> alXplbmYyb = new ArrayList<String>();

        final ArrayList<String> alLb2 = new ArrayList<String>();
        final ArrayList<Integer> aiSC2 = new ArrayList<Integer>();
        final ArrayList<String> alLbYyb2 = new ArrayList<String>();
        final ArrayList<Integer> aiSCYyb2 = new ArrayList<Integer>();
        final ArrayList<Integer> aiYyb2 = new ArrayList<Integer>();
        final ArrayList<Integer> aiPz2 = new ArrayList<Integer>();
        final ArrayList<Integer> aiPzYyb2 = new ArrayList<Integer>();
        final ArrayList<String> alXplbm2 = new ArrayList<String>();
        final ArrayList<String> alXplbmYyb2 = new ArrayList<String>();

        List lstbiUnsalable;
        List lstbiUnsalableYyb;
        sDate = new SimpleDateFormat("w").format(adTsnDate);
        aiWeek = Integer.parseInt(sDate);
        //cal.set(iRc, iRc, iRc);

        System.out.println("now processing    biUnsalableSymbol's Data(step 1/10)... Date:" + adTsnDate +
                " shopid;" + aiShopId + " please wait...");
        aiYear = Integer.parseInt(asYear);
        //取普通商品总销售金额
        JdbcTemplate jt = getJdbcTemplate();
        sql = "select a.lb,nvl(count(a.bm),0) as kc_cnt,substr(b.xpzhbm,1,3) as xplbm,c.pz" +
                " from " +asTname+ " a,sy_spk b,sy_archi_dept c " +
                "where a.lb=b.lb and a.bm=c.bm and c.ckbj=1 and  a.spkdate=? " +
                "and a.sl<>0 and a.lb is not null group by a.lb ,b.xpzhbm,c.pz";
        Object[] params = new Object[]{adTsnDate};
        int[] types = {Types.DATE};
        lstbiUnsalable = jt.query(sql, params, types, new RowMapperResultReader(new biUnsalableRM()));
        Iterator Iter = lstbiUnsalable.iterator();
        biUnsalable ltam = new biUnsalable();
        while (Iter.hasNext()) {
            ltam = (biUnsalable) Iter.next();
            sLb = ltam.getLb();
            sXplbm = ltam.getXplbm();
            iShopCnt = ltam.getKc_cnt();
            iPz = ltam.getPz();
            alLb.add(sLb);
            aiSC.add(iShopCnt);
            alXplbm.add(sXplbm);
            aiPz.add(iPz);
            //cls
            sLb = "";
            iShopCnt = 0;
            sXplbm = "";
            iPz = 0;
        }
        //取自采销售部分数据
        lstbiUnsalable.clear();
        sql = "select a.lb,nvl(count(a.bm),0) as kc_cnt,'000' as xplbm,c.pz" +
                " from " +asTname+ " a,sy_archi_dept c " +
                "where a.bm=c.bm and c.ckbj=1 and  a.spkdate=? " +
                "and a.sl<>0 and substr(a.lb,1,1)='5'  group by a.lb,c.pz";
        lstbiUnsalable = jt.query(sql, params, types, new RowMapperResultReader(new biUnsalableRM()));
        Iter = lstbiUnsalable.iterator();
        ltam = new biUnsalable();
        while (Iter.hasNext()) {
            ltam = (biUnsalable) Iter.next();
            sLb = ltam.getLb();
            sXplbm = ltam.getXplbm();
            iShopCnt = ltam.getKc_cnt();
            iPz = ltam.getPz();
            alLb.add(sLb);
            aiSC.add(iShopCnt);
            alXplbm.add(sXplbm);
            aiPz.add(iPz);
            //cls
            sLb = "";
            iShopCnt = 0;
            sXplbm = "";
            iPz = 0;
        }
        
        
        
        
        System.out.println("now processing    biUnsalableSymbol's Data(step 2/10)... Date:" + adTsnDate +
                " shopid;" + aiShopId + " please wait...");

        //取各营运部和配置分别计算的
        sql = "select a.lb,nvl(count(a.bm),0) as kc_cnt,substr(b.xpzhbm,1,3) as xplbm,c.pz,c.yybh" +
                " from " +asTname+ " a,sy_spk b,sy_archi_dept c " +
                "where a.lb=b.lb and a.bm=c.bm and c.ckbj=1 and  a.spkdate=? " +
                "and a.sl<>0 and a.lb is not null group by a.lb ,b.xpzhbm,c.pz,c.yybh";
        lstbiUnsalableYyb = jt.query(sql, params, types, new RowMapperResultReader(new biUnsalableRM2()));
        Iterator Iter2 = lstbiUnsalableYyb.iterator();
        biUnsalable ltam2 = new biUnsalable();
        while (Iter2.hasNext()) {
            ltam2 = (biUnsalable) Iter2.next();
            sLb = ltam2.getLb();
            iShopCnt = ltam2.getKc_cnt();
            iYybh = ltam2.getYybh();
            iPz = ltam2.getPz();
            sXplbm = ltam2.getXplbm();
            alLbYyb.add(sLb);
            aiSCYyb.add(iShopCnt);
            aiYyb.add(iYybh);
            aiPzYyb.add(iPz);
            alXplbmYyb.add(sXplbm);
            //cls
            sLb = "";
            iShopCnt = 0;
            iYybh = 0;
            iPz = 0;
            sXplbm = "";
        }
        //取自采部分
        lstbiUnsalableYyb.clear();
        sql = "select a.lb,nvl(count(a.bm),0) as kc_cnt,'000' as xplbm,c.pz,c.yybh" +
                " from " +asTname+ " a,sy_archi_dept c " +
                "where a.bm=c.bm and c.ckbj=1 and  a.spkdate=? " +
                "and a.sl<>0 and substr(a.lb,1,1)='5'  group by a.lb,c.pz,c.yybh";
        lstbiUnsalableYyb = jt.query(sql, params, types, new RowMapperResultReader(new biUnsalableRM2()));
        Iter2 = lstbiUnsalableYyb.iterator();
        ltam2 = new biUnsalable();
        while (Iter2.hasNext()) {
            ltam2 = (biUnsalable) Iter2.next();
            sLb = ltam2.getLb();
            iShopCnt = ltam2.getKc_cnt();
            iYybh = ltam2.getYybh();
            iPz = ltam2.getPz();
            sXplbm = ltam2.getXplbm();
            alLbYyb.add(sLb);
            aiSCYyb.add(iShopCnt);
            aiYyb.add(iYybh);
            aiPzYyb.add(iPz);
            alXplbmYyb.add(sXplbm);
            //cls
            sLb = "";
            iShopCnt = 0;
            iYybh = 0;
            iPz = 0;
            sXplbm = "";
        }
        
        
        
        
        
        System.out.println("now processing    biUnsalableSymbol's Data(step 3/10)... Date:" + adTsnDate +
                " shopid;" + aiShopId + " please wait...");

        //begin insert into biUnsalable
        //1:delete
        sql = "delete from t_bi_unsalable_symbol where tsn_date = ? ";
        iRc = jt.update(sql, params, types);
        System.out.println("now processing    biUnsalableSymbol's Data(step 4/10)... Date:" + adTsnDate +
                " shopid;" + aiShopId + " please wait...");
       
        //2:insert
        iyear = aiYear;
        iweek = aiWeek;
        dd = adTsnDate;
        //总的
        isize = alLb.size();
        for (i = 0; i < isize; i++) {
            alLb2.add(alLb.get(i));
            aiSC2.add(aiSC.get(i));
            alXplbm2.add(alXplbm.get(i));
            aiPz2.add(aiPz.get(i));
        }
        if (isize > 0) {
            sql = "insert into t_bi_unsalable_symbol " +
                    "(tsn_year,tsn_week,tsn_date,lb,kc_cnt,yybh,pz,xplbm)" +
                    " values(?,?,?,?,?,?,?,?)";
            BatchPreparedStatementSetter setter = null;
            setter = new BatchPreparedStatementSetter() {

                public int getBatchSize() {
                    return isize;
                }

                public void setValues(PreparedStatement ps, int index) throws SQLException {
                    ps.setInt(1, iyear);
                    ps.setInt(2, iweek);
                    ps.setDate(3, dd);
                    ps.setString(4, alLb2.get(index));
                    ps.setInt(5, aiSC2.get(index));
                    ps.setInt(6, 0);
                    ps.setInt(7, aiPz2.get(index));
                    ps.setString(8, alXplbm2.get(index));
                }
            };
            jt.batchUpdate(sql, setter);
        }
        System.out.println("now processing    biUnsalableSymbol's Data(step 5/10)... Date:" + adTsnDate +
                " shopid;" + aiShopId + " please wait...");

        //分的
        isize2 = alLbYyb.size();
        for (i = 0; i < isize2; i++) {
            alLbYyb2.add(alLbYyb.get(i));
            aiSCYyb2.add(aiSCYyb.get(i));
            aiYyb2.add(aiYyb.get(i));
            aiPzYyb2.add(aiPzYyb.get(i));
            alXplbmYyb2.add(alXplbmYyb.get(i));
        }
        if (isize2 > 0) {
            BatchPreparedStatementSetter setter2 = null;
            setter2 = new BatchPreparedStatementSetter() {

                public int getBatchSize() {
                    return isize2;
                }

                public void setValues(PreparedStatement ps, int index) throws SQLException {
                    ps.setInt(1, iyear);
                    ps.setInt(2, iweek);
                    ps.setDate(3, dd);
                    ps.setString(4, alLbYyb2.get(index));
                    ps.setInt(5, aiSCYyb2.get(index));
                    ps.setInt(6, aiYyb2.get(index));
                    ps.setInt(7, aiPzYyb2.get(index));
                    ps.setString(8, alXplbmYyb2.get(index));
                }
                };
            jt.batchUpdate(sql, setter2);
        }
        System.out.println("now processing    biUnsalableSymbol's Data(step 6/10)... Date:" + adTsnDate +
                " shopid;" + aiShopId + " please wait...");
        
        //3:重新生成配置表
        sql = "truncate table t_bi_unsalable_pz_symbol";
        iRc = jt.update(sql);
        System.out.println("now processing    biUnsalableSymbol's Data(step 7/10)... Date:" + adTsnDate +
                " shopid;" + aiShopId + " please wait...");
        
        sql = "insert into t_bi_unsalable_pz_symbol   " +
                "SELECT   a.lb, nvl(COUNT (a.lb),0) , b.pz , b.yybh  " +
                "FROM sy_main_config_usebyshop a, t_shop_symbol b  " +
                "WHERE a.ID = b.ID  and a.bj=1 and a.yhfs < 4 GROUP BY a.lb, b.pz , b.yybh";
        iRc = jt.update(sql);
        
        
        //5:insert the pz_cnt total data
        /*
        sql = "insert into t_bi_unsalable_symbol " +
                "select tsn_year,tsn_week,lb,sum(pz_cnt),sum(kc_cnt),0,pz,xplbm,tsn_date " +
                "from t_bi_unsalable_symbol where tsn_date = ?  " +
                "group by tsn_year,tsn_week,lb,xplbm,tsn_date,pz";
        iRc = jt.update(sql, params, types);
        */
        System.out.println("now processing    biUnsalableSymbol's Data(step 8/10)... Date:" + adTsnDate +
                " shopid;" + aiShopId + " please wait...");
        
        sql = "update t_bi_unsalable_symbol a set a.pz_cnt = " +
                "(select b.pz_cnt from t_bi_unsalable_pz_symbol b where a.lb=b.lb " +
                "and a.pz=b.pz and a.yybh=b.yybh) where a.tsn_date = ? ";
        iRc = jt.update(sql, params, types);

        System.out.println("now processing    biUnsalableSymbol's Data(step 9/10)... Date:" + adTsnDate +
        " shopid;" + aiShopId + " please wait...");
        
        sql = "update t_bi_unsalable_symbol a set a.pz_cnt = " +
                "(select nvl(sum(b.pz_cnt),0) from t_bi_unsalable_pz_symbol b where a.lb=b.lb " +
                "and a.pz=b.pz ) where a.tsn_date = ? and a.yybh=0 ";
        iRc = jt.update(sql, params, types);

        
        System.out.println("now processing    biUnsalableSymbol's Data(step 10/10)... Date:" + adTsnDate +
                " shopid;" + aiShopId + " please wait...");
        //6:insert the total data
        sql = "insert into t_bi_unsalable_symbol " +
                "select tsn_year,tsn_week,lb,sum(pz_cnt),sum(kc_cnt),0,0,xplbm,tsn_date " +
                "from t_bi_unsalable_symbol where tsn_date = ? and yybh=0 " +
                "group by tsn_year,tsn_week,lb,xplbm,tsn_date";
        iRc = jt.update(sql, params, types);


    }

    class biUnsalableRM implements RowMapper {

        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            biUnsalable lmx = new biUnsalable();
            lmx.setLb(rs.getString("lb").trim());
            lmx.setKc_cnt(rs.getInt("kc_cnt"));
            lmx.setXplbm(rs.getString("xplbm"));
            lmx.setPz(rs.getInt("pz"));
            return lmx;
        }
    }

    class biUnsalableRM2 implements RowMapper {

        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            biUnsalable lmx = new biUnsalable();
            lmx.setLb(rs.getString("lb").trim());
            lmx.setKc_cnt(rs.getInt("kc_cnt"));
            lmx.setYybh(rs.getInt("yybh"));
            lmx.setXplbm(rs.getString("xplbm"));
            lmx.setPz(rs.getInt("pz"));
            return lmx;
        }
    }
}
